package com.sunnystudio.mydiary.data;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DBManager {
	private SQLiteDatabase db;
	private DBHelper dbHelper;

	/**
	 * Constructor for Application Database Adapter
	 * 
	 * @param _context  
	 */
	public DBManager(Context _context) {
		dbHelper = new DBHelper(_context, DBConstants.DATABASE_NAME, null, DBConstants.DATABASE_VERSION);
		open();
	}
	
	/**
	 * Open the database
	 * 
	 * @throws SQLiteException
	 */
	public SQLiteDatabase open() throws SQLiteException {
		if (db == null || !db.isOpen()) {
			try {
				db = dbHelper.getWritableDatabase();
			} catch (SQLiteException ex) {
				db = dbHelper.getReadableDatabase();
			}
		}
		
		return db;
	}
	
	void rebuild()
	{
		dbHelper.onUpgrade(db, 0, 1);
	}
	
	/**
	 * 
	 * Open / Update database
	 * 
	 * @ClassName: DBHelper
	 * @Description: 
	 *
	 */
	private static class DBHelper extends SQLiteOpenHelper {

		public DBHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		// Store the messages sent from server
		private static final String MESSAGE_TABLE = "create table if not exists " + DBConstants.TABLE_MESSAGE + 
				" (" +
				"_id integer primary key autoincrement, " +
				"message_id integer not null, " +
				"parent_message_id integer not null, " +
				"is_read integer, " +
				"is_require_response integer, " +
				"priority integer, " +
				"content_id integer, " +
				"language text, " +
				"encoding int, " +
				"message_from_type int, " +
			    "message_from text not null, " +
			    "message_to_type int , " +
			    "message_to text not null, " +
			    "time integer, " +
			    "subject text not null, " +
			    "body text not null);";
		
		// Store the alerts sent from server
		private static final String ALERT_TABLE = "create table if not exists " + DBConstants.TABLE_ALERT + 
				" (" +
				"_id integer primary key autoincrement, " +
				"cfms_id real, " + // CFMS key
				"alert_type text not null, " +
				"timestamp long not null, " +
				"shift_id text not null, " +
				"odometer integer not null, " +
				"latitude real, " +
				"longitude real, " +
			    "driver_id text not null, " +
			    "driver_id_type int not null, " +
			    "duration real, " +
			    "distance_travelled real, " +
			    "max_speed real," + 
			    "is_read integer );";
		
		// Store snapshot summary of driver report
		private static final String SNAPSHOT_SUMMARY_TABLE = "create table if not exists " + DBConstants.TABLE_SNAPSHOT_SUMMARY + 
				" (" +
				"_id integer primary key autoincrement, " +
				"timestamp long, " +
				"data text, " +
				"is_last_shift_period integer, " +
				"sequence_num integer, " +
				"shift_id text not null, " +
			    "vperf_message_version integer, " +
			    "cfms_trans_id long );";
		
		// Store shift abstract of driver report
		private static final String SHIFT_ABSTRACT_TABLE = "create table if not exists " + DBConstants.TABLE_SHIFT_ABSTRACT + 
				" (" +
				"_id integer primary key autoincrement, " +
				"shift_id text, " +
				"logon_time long, " +
				"logoff_time long, " +
				"driver_id_type integer, " +
				"driver_id text, " +
				"total_distance_travelled real, " +
				"total_duration integer, " +
			    "idling_time integer, " +
			    "over_rev_distance real, " +
			    "harsh_throttle_distance real, " +
			    "braking_distance real, " +
			    "cruise_control_distance real," + 
			    "coasting_distance real, " +
			    "harsh_cold_drv_distance real, " +
			    "eco_drive_distance real, " +
			    "harsh_acc_distance real, " +
			    "harsh_brake_distance real, " +
			    "obu_id integer, " +
			    "crc long );";
		
		// TODO, may not need
		private static final String TIPS_TABLE = "create table if not exists " + DBConstants.TABLE_TIPS + 
				" (" +
				"_id integer primary key autoincrement, " +
				"tip_number integer, " +
				"tip_category integer, " +
				"tip_level integer, " +
				"tip_language integer, " +
				"tip_text text );";
		
		// Store targets and weights for calculate grade of driver report
		private static final String TARGET_TABLE = "create table if not exists " + DBConstants.TABLE_TARGET + 
				" (" +
				"_id integer primary key autoincrement, " +
				"version_num integer not null, " +
				"target_braking_distance real, " +
				"target_coasting real, " +
				"target_cruise_ctl real, " +
				"target_ecoband_drv real, " +
				"target_harsh_acceleration real, " +
				"target_harsh_braking real, " +
				"target_harsh_cold_drv real, " +
				"target_harsh_throt real, " +
				"target_idling_percent real, " +
				"target_overrev real, " +
				"weight_braking_distance real, " +
				"weight_coasting_distance real, " +
				"weight_cruise_ctl_distance real, " +
				"weight_eco_drive_distance real, " +
				"weight_harsh_acc_distance real, " +
				"weight_harsh_brake_distance real, " +
				"weight_harsh_cold_drv_distance real, " +
				"weight_harsh_throt_distance real, " +
				"weight_idling_time real, " +
				"weight_over_rev_distance real, " +
				"target_grade real );";
		
		// TODO, may not need
		// Store the reply with options
		private static final String REPLYWITH_TABLE = "create table if not exists " + DBConstants.TABLE_REPLYWITH + 
				" (" +
				"_id integer primary key autoincrement, " +
				"language text not null, " +
				"name text not null, " +
				"field_order integer);";
		
		// Store the messages need to re-send to server
		private static final String RELIABLEMESSAGE_TABLE = "create table if not exists " + DBConstants.TABLE_RELIABLE_MESSAGE + 
				" (" +
				"_id integer primary key autoincrement, " +
				"transaction_id integer not null, " + // The transaction id of this message being sent to Choreo
				"priority integer not null, " +
				"retries integer, " +
				"message_data text not null, " +
				"message_id integer, " +   // i.e. message type 0 .. 255
				"reference_id integer );"; // reference id used by app layer to find outbound messages to related app logic
		
		// Store the user config
		private static final String CONFIG_TABLE = "create table if not exists " + DBConstants.TABLE_CONFIG + 
				" (" +
				"_id integer primary key autoincrement, " +
				"driver_id text not null, " +
				"driver_type integer, " +
				"is_eula_accepted integer not null, " +
				"language text, " +
				"audio integer, " +
				"show_high_msg integer, " +
				"show_high_alert integer);";

		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(MESSAGE_TABLE);
			_db.execSQL(ALERT_TABLE);
			_db.execSQL(SNAPSHOT_SUMMARY_TABLE);
			_db.execSQL(SHIFT_ABSTRACT_TABLE);
			_db.execSQL(TARGET_TABLE);
			
			_db.execSQL(TIPS_TABLE);
			_db.execSQL(REPLYWITH_TABLE);
			_db.execSQL(RELIABLEMESSAGE_TABLE);
			_db.execSQL(CONFIG_TABLE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
				int _newVersion) {
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_MESSAGE);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_ALERT);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_SNAPSHOT_SUMMARY);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_SHIFT_ABSTRACT);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_TARGET);
			 
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_TIPS);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_REPLYWITH);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_RELIABLE_MESSAGE);
			 _db.execSQL("drop table if exists " + DBConstants.TABLE_CONFIG);
			 
			 onCreate(_db);
		}
	}
}
